/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Course;

import Department.DepartmentBean;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static int add(CourseBean cb) throws SQLException {
        int i = 0, k = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into course values(courseid.nextval,?,?,?,?,sysdate)");
            pre.setString(1, cb.getCourseName());
            pre.setInt(2, cb.getCourseType());
            pre.setInt(3, cb.getCoursePeriod());
            pre.setString(4, cb.getStream());
            k = pre.executeUpdate();
            if (k != 0) {
                i = getMaxId();
            }


        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;

    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            PreparedStatement pre = con.prepareStatement("select max(course_id) from course");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                i = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return i;
    }

    public static CourseBean viewSpecific(String cname, String val) throws SQLException {
        CourseBean cb = new CourseBean();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from course where " + cname + "=?");
            pre.setString(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                cb.setCourseId(rs.getInt(1));
                cb.setCourseName(rs.getString(2));
                cb.setCourseType(rs.getInt(3));
                cb.setCoursePeriod(rs.getInt(4));
                cb.setStream(rs.getString(5));
                cb.setEntryDate(rs.getDate(6));
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return cb;
    }

    public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from course order by course_id");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                CourseBean cb = new CourseBean();
                cb.setCourseId(rs.getInt(1));
                cb.setCourseName(rs.getString(2));
                cb.setCourseType(rs.getInt(3));
                cb.setCoursePeriod(rs.getInt(4));
                cb.setStream(rs.getString(5));
                cb.setEntryDate(rs.getDate(6));
                list.add(cb);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
    public static Vector viewAlldistinct() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select distinct (course_name) from course");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                CourseBean cb = new CourseBean();
                cb.setCourseName(rs.getString(1));
                list.add(cb);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
    

    public static int edit(CourseBean cb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update course set course_name=?, course_type=?, course_period=?, stream=?, entrydate=sysdate where course_id=?");
            pre.setString(1, cb.getCourseName());
            pre.setInt(2, cb.getCourseType());
            pre.setInt(3, cb.getCoursePeriod());
            pre.setString(4, cb.getStream());
            pre.setInt(5, cb.getCourseId());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int delete(int id) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("delete from COURSE where COURSE_ID=?");
            pre.setInt(1, id);
            i = pre.executeUpdate();
        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }

    public static Vector getStream(String course) throws SQLException {
        Vector<String> list = new Vector<String>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select course_id, stream from course where course_name=?");
            pre.setString(1, course);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                list.add(rs.getString(1));
                list.add(rs.getString(2));
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
    
    public static int getCoursePeriod(int course) throws SQLException {
        int period=0;
        PreparedStatement pre =null;
        try {
            con = DBConnection.Connection.getConnection();
            if(course==0)
            {
              pre = con.prepareStatement("select max(course_period) from course");   
            }
            else
            {
             pre = con.prepareStatement("select course_period from course where course_id=?");
             pre.setInt(1, course);
            }
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                period =rs.getInt(1);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return period;
    }
    public static int getCoursePeriod(String course, String stream) throws SQLException {
        int period=0;
        PreparedStatement pre =null;
        try {
            con = DBConnection.Connection.getConnection();
           
             pre = con.prepareStatement("select course_period from course where course_name=? and stream=?");
             pre.setString(1, course);
             pre.setString(2, stream);
            
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                period =rs.getInt(1);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return period;
    }
    
    public static Vector viewAlldistinctFees() throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select distinct (course_name) from course");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                CourseBean cb = new CourseBean();
                cb.setCourseName(rs.getString(1));
                list.add(cb);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
    
    
     public static Vector viewSearch(int courseid) throws SQLException {
        Vector<Object> list = new Vector<Object>();

        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from course where course_id=?");
            pre.setInt(1, courseid);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                CourseBean cb = new CourseBean();
                cb.setCourseId(rs.getInt(1));
                cb.setCourseName(rs.getString(2));
                cb.setCourseType(rs.getInt(3));
                cb.setCoursePeriod(rs.getInt(4));
                cb.setStream(rs.getString(5));
                cb.setEntryDate(rs.getDate(6));
                list.add(cb);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
}
